import os
from core.database import DatabaseManager,OpenGaussManager
import pandas as pd
#from sklearn.model_selection import train_test_split
from sklearn import svm
from sklearn import metrics
from sklearn.model_selection import train_test_split
import numpy as np

# in jupyter notebook current_path get with dir '.'
# current_path = os.path.abspath('.')
current_path = os.path.dirname(os.path.realpath(__file__)) 
def start_training(train, test) -> tuple[float, np.ndarray]:
    train_x = train[['sepallengthcm', 'sepalwidthcm', 'petallengthcm', 'petalwidthcm']]
    train_y = train.species
    test_x = test[['sepallengthcm', 'sepalwidthcm', 'petallengthcm', 'petalwidthcm']]
    test_y = test.species
    # 创建支持向量机（SVM）分类器模型
    model = svm.SVC()
    # 在训练集上拟合SVM模型
    model.fit(train_x, train_y)
    # 使用训练好的模型对测试集进行预测
    prediction = model.predict(test_x)
    return metrics.accuracy_score(prediction, test_y),prediction

def check_prediction(prediction: np.ndarray, test: pd.DataFrame):
    raw=test.species.tolist()
    for idx, specie in enumerate(raw):
        is_euqal=False
        if  prediction[idx]==specie:
            is_euqal=True
        print(idx,is_euqal, specie, prediction[idx])

def get_iris_from_csv(filename: str)->list[pd.DataFrame]:
    iris=pd.read_csv(filename)
    iris.columns=[col.lower() for col in iris.columns]
    return train_test_split(iris, test_size=0.2)

def get_iris_from_pg(current_path: str)->list[pd.DataFrame]:
    manager=OpenGaussManager(current_path+"/config.yaml")
    #train = pd.read_csv(current_path+'/train.csv')
    # 从表iris_train中读取鸢尾花训练数据集
    #train_table="iris_train"
    #test_table="iris_test"
    try:
        #conn=manager.connect()
        engine=manager.create_engine()
        train=pd.read_sql('''SELECT * FROM  iris_train;''', con = engine,index_col="id")
        print(train)
        test=pd.read_sql('''SELECT * FROM  iris_test;''', con = engine,index_col="id")
        print(test)
    except ConnectionError as e:
        print(f"\n连接失败: {e}")
    finally:
        manager.close()
        print("\n连接已关闭")
    return [train, test]

if __name__ == "__main__":
    train, test = get_iris_from_csv(current_path+'/../data/Iris.csv')
    #print(train,test)
    accuracy, prediction=start_training(train, test)
    check_prediction(prediction, test)
    print('The accuracy of the SVM is: %f.'% accuracy)
